using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static ImovelxfotoVO getImovelxfoto(int? idImovelxfoto)
		{
            return getImovelxfoto(idImovelxfoto, null);
		}
        public static ImovelxfotoVO getImovelxfoto(int? idImovelxfoto, int? idImovel)
        {
            ImovelxfotoVO imovelxfotoVO = null;
            List<ImovelxfotoVO> listImovelxfotoVOs = getImovelxfotos(idImovelxfoto, idImovel);
            if (listImovelxfotoVOs != null && listImovelxfotoVOs.Count > 0)
            {
                imovelxfotoVO = listImovelxfotoVOs[0];
            }
            return imovelxfotoVO;
        }

		public static List<ImovelxfotoVO> getImovelxfotos()
		{
			return getImovelxfotos(null, null);
		}

		public static List<ImovelxfotoVO> getImovelxfotos(int? idImovelxfoto)
		{
			return getImovelxfotos(idImovelxfoto, null);
		}

		public static List<ImovelxfotoVO> getImovelxfotos(int? idImovelxfoto, int? idImovel)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT imof.idImovelxfoto as imof_idImovelxfoto, imof.idImovel as imof_idImovel, imo2.titulo as imo2_titulo, imof.fotoLargeMime as imof_fotoLargeMime, imof.fotoLargeNome as imof_fotoLargeNome , imof.fotoMediumMime as imof_fotoMediumMime, imof.fotoMediumNome as imof_fotoMediumNome , imof.fotoSmallMime as imof_fotoSmallMime, imof.fotoSmallNome as imof_fotoSmallNome , imof.Cadastro as imof_Cadastro, imof.CadastradoPor as imof_CadastradoPor, imof.Edicao as imof_Edicao, imof.EditadoPor as imof_EditadoPor  ");
			query.AppendLine("FROM imovelxfoto as imof LEFT OUTER JOIN imovel as imo2 on imof.idImovel=imo2.idImovel  ");
			if (idImovelxfoto != null)
			{
				query.AppendLine(where + "imof.idImovelxfoto=@idImovelxfoto ");
				where = "AND ";
			}
			if (idImovel != null)
			{
				query.AppendLine(where + "imof.idImovel=@idImovel ");
				where = "AND ";
			}


			query.AppendLine("ORDER BY imof.idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			if (idImovelxfoto!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", idImovelxfoto));
			if (idImovel!= null) cmd.Parameters.Add(getParameter(cmd, "@idImovel", idImovel));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ImovelxfotoVO> listImovelxfoto = new List<ImovelxfotoVO>();

				while (reader.Read())
				{
					ImovelxfotoVO imovelxfotoVO = ImovelxfotoVO.CreateVO();
					imovelxfotoVO.idImovelxfoto = reader.GetInt32(reader.GetOrdinal("imof_idImovelxfoto"));
					imovelxfotoVO.idImovel = reader.GetInt32(reader.GetOrdinal("imof_idImovel"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo2_titulo")))
						imovelxfotoVO.idImovelVO.titulo = reader.GetString(reader.GetOrdinal("imo2_titulo"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoLargeMime")))
						imovelxfotoVO.fotoLargeMime = reader.GetString(reader.GetOrdinal("imof_fotoLargeMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoLargeNome")))
						imovelxfotoVO.fotoLargeNome = reader.GetString(reader.GetOrdinal("imof_fotoLargeNome"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoMediumMime")))
						imovelxfotoVO.fotoMediumMime = reader.GetString(reader.GetOrdinal("imof_fotoMediumMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoMediumNome")))
						imovelxfotoVO.fotoMediumNome = reader.GetString(reader.GetOrdinal("imof_fotoMediumNome"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoSmallMime")))
						imovelxfotoVO.fotoSmallMime = reader.GetString(reader.GetOrdinal("imof_fotoSmallMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoSmallNome")))
						imovelxfotoVO.fotoSmallNome = reader.GetString(reader.GetOrdinal("imof_fotoSmallNome"));
					imovelxfotoVO.cadastro = reader.GetDateTime(reader.GetOrdinal("imof_Cadastro"));
					imovelxfotoVO.cadastradoPor = reader.GetString(reader.GetOrdinal("imof_CadastradoPor"));
					imovelxfotoVO.edicao = reader.GetDateTime(reader.GetOrdinal("imof_Edicao"));
					imovelxfotoVO.editadoPor = reader.GetString(reader.GetOrdinal("imof_EditadoPor"));


					listImovelxfoto.Add(imovelxfotoVO);
				}
				reader.Close();
				return listImovelxfoto;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelxfoto(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxfoto ");
			query.AppendLine("SET idImovel=@idImovel, fotoLargeMime=@fotoLargeMime, fotoLargeNome=@fotoLargeNome, fotoMediumMime=@fotoMediumMime, fotoMediumNome=@fotoMediumNome, fotoSmallMime=@fotoSmallMime, fotoSmallNome=@fotoSmallNome,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));
			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelxfotoVO.idImovel));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeMime", imovelxfotoVO.fotoLargeMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeNome", imovelxfotoVO.fotoLargeNome));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumMime", imovelxfotoVO.fotoMediumMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumNome", imovelxfotoVO.fotoMediumNome));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallMime", imovelxfotoVO.fotoSmallMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallNome", imovelxfotoVO.fotoSmallNome));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelxfotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelxfotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelxfotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelxfotoVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newImovelxfoto(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO imovelxfoto ");
			query.AppendLine("  ( idImovel, fotoLargeMime, fotoLargeNome, fotoMediumMime, fotoMediumNome, fotoSmallMime, fotoSmallNome, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @idImovel, @fotoLargeMime, @fotoLargeNome, @fotoMediumMime, @fotoMediumNome, @fotoSmallMime, @fotoSmallNome, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelxfotoVO.idImovel));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeMime", imovelxfotoVO.fotoLargeMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeNome", imovelxfotoVO.fotoLargeNome));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumMime", imovelxfotoVO.fotoMediumMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumNome", imovelxfotoVO.fotoMediumNome));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallMime", imovelxfotoVO.fotoSmallMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallNome", imovelxfotoVO.fotoSmallNome));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelxfotoVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelxfotoVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelxfotoVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelxfotoVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('imovelxfoto') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delImovelxfoto(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM imovelxfoto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
		public static int getCountImovelxfotoxIdImovel(int? idImovel)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idImovel) as ocorrencias FROM imovelxfoto WHERE IdImovel=@IdImovel ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdImovel", idImovel));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
		public static void setImovelxfotoFotoLarge(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxfoto ");
			query.AppendLine("SET fotoLarge=@fotoLarge, fotoLargeMime=@fotoLargeMime, fotoLargeNome=@fotoLargeNome ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));

			cmd.Parameters.Add(getParameter(cmd, "@fotoLarge", imovelxfotoVO.fotoLarge));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeMime", imovelxfotoVO.fotoLargeMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoLargeNome", imovelxfotoVO.fotoLargeNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void getImovelxfotoFotoLarge(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT fotoLarge as imof_fotoLarge, fotoLargeMime as imof_fotoLargeMime, fotoLargeNome as imof_fotoLargeNome ");
			query.AppendLine("FROM imovelxfoto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoLarge")))
						imovelxfotoVO.fotoLarge = getBytesFromBlob(reader, "imof_fotoLarge");

					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoLargeMime")))
						imovelxfotoVO.fotoLargeMime = reader.GetString(reader.GetOrdinal("imof_fotoLargeMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoLargeNome")))
						imovelxfotoVO.fotoLargeNome = reader.GetString(reader.GetOrdinal("imof_fotoLargeNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelxfotoFotoMedium(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxfoto ");
			query.AppendLine("SET fotoMedium=@fotoMedium, fotoMediumMime=@fotoMediumMime, fotoMediumNome=@fotoMediumNome ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));

			cmd.Parameters.Add(getParameter(cmd, "@fotoMedium", imovelxfotoVO.fotoMedium));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumMime", imovelxfotoVO.fotoMediumMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoMediumNome", imovelxfotoVO.fotoMediumNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void getImovelxfotoFotoMedium(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT fotoMedium as imof_fotoMedium, fotoMediumMime as imof_fotoMediumMime, fotoMediumNome as imof_fotoMediumNome ");
			query.AppendLine("FROM imovelxfoto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoMedium")))
						imovelxfotoVO.fotoMedium = getBytesFromBlob(reader, "imof_fotoMedium");

					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoMediumMime")))
						imovelxfotoVO.fotoMediumMime = reader.GetString(reader.GetOrdinal("imof_fotoMediumMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoMediumNome")))
						imovelxfotoVO.fotoMediumNome = reader.GetString(reader.GetOrdinal("imof_fotoMediumNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelxfotoFotoSmall(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxfoto ");
			query.AppendLine("SET fotoSmall=@fotoSmall, fotoSmallMime=@fotoSmallMime, fotoSmallNome=@fotoSmallNome ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));

			cmd.Parameters.Add(getParameter(cmd, "@fotoSmall", imovelxfotoVO.fotoSmall));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallMime", imovelxfotoVO.fotoSmallMime));
			cmd.Parameters.Add(getParameter(cmd, "@fotoSmallNome", imovelxfotoVO.fotoSmallNome));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void getImovelxfotoFotoSmall(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT fotoSmall as imof_fotoSmall, fotoSmallMime as imof_fotoSmallMime, fotoSmallNome as imof_fotoSmallNome ");
			query.AppendLine("FROM imovelxfoto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoSmall")))
						imovelxfotoVO.fotoSmall = getBytesFromBlob(reader, "imof_fotoSmall");

					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoSmallMime")))
						imovelxfotoVO.fotoSmallMime = reader.GetString(reader.GetOrdinal("imof_fotoSmallMime"));
					if (!reader.IsDBNull(reader.GetOrdinal("imof_fotoSmallNome")))
						imovelxfotoVO.fotoSmallNome = reader.GetString(reader.GetOrdinal("imof_fotoSmallNome"));

				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

		public static void setImovelxfotoFoto(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovelxfoto ");
			query.AppendLine("SET foto=@foto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));

			cmd.Parameters.Add(getParameter(cmd, "@foto", imovelxfotoVO.foto));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void getImovelxfotoFoto(ImovelxfotoVO imovelxfotoVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT foto as imof_foto ");
			query.AppendLine("FROM imovelxfoto ");
			query.AppendLine("WHERE idImovelxfoto=@idImovelxfoto ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovelxfoto", imovelxfotoVO.idImovelxfoto));


			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				if (reader.Read())
				{
					if (!reader.IsDBNull(reader.GetOrdinal("imof_foto")))
						imovelxfotoVO.foto = getBytesFromBlob(reader, "imof_foto");


				}
				reader.Close();
				return;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}

	}
}
